﻿using FC.NPOI.Utils.Exceptions;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace FC.NPOI.Utils
{
    /// <summary>
    /// office Excel 帮助类
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 将 HttpPostedFileBase 转化为 Model List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file"></param>
        /// <returns></returns>
        public static IList<T> ToModelList<T>(this HttpPostedFileBase file) where T : class
        {

            try
            {
                IWorkbook workbook = file.CreateWorkbook();
                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet != null)
                    return sheet.ToModelList<T>();
                else
                    throw new ExcelHelperException("文件中不包含任何标签页");

            }
            catch (Exception e)
            {
                throw e;
            }

            
        }

        /// <summary>
        /// 将 Excel 表 转化为 Model List 特别说明：Model 中的属性类型应为 string
        /// </summary>
        /// <typeparam name="T">Model</typeparam>
        /// <param name="sheet">表</param>
        /// <param name="list">list</param>
        /// <returns></returns>
        public static IList<T> ToModelList<T>(this ISheet sheet) where T : class
        {
            IList<T> list = new List<T>();

            if (sheet != null && sheet.LastRowNum >= 0)
            {
                Type type = typeof(T);
                PropertyInfo[] properties = type.GetProperties();
                IRow firstRow = sheet.GetRow(0);

                Dictionary<string, int> dictionary = new Dictionary<string, int>();
                for (int i = 0; i < firstRow.LastCellNum; i++)
                {
                    ICell cell = firstRow.GetCell(i);
                    if (cell != null) dictionary.Add(cell.ToString().ToUpper(), i);
                }
                string state = "";

                foreach (var property in properties)
                {
                    string key = dictionary.Keys.FirstOrDefault(p => p.Equals(property.Name, StringComparison.CurrentCultureIgnoreCase));
                    if (key == null)
                    {
                        state += $"缺少 {property.Name} 列的信息\n";
                    }
                }
                if (!string.IsNullOrWhiteSpace(state))
                    throw new ExcelHelperException(state);

                for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            T obj = Activator.CreateInstance<T>();
                            foreach (var property in properties)
                            {
                                ICell cell = row.GetCell(dictionary[property.Name.ToUpper()]);
                                property.SetValue(obj, cell == null ? null : cell.ToString(), null);
                            }
                            list.Add(obj);
                        }
                    }
                }
            else
                throw new ExcelHelperException("表不能为空");
            return list;
        }

        /// <summary>
        /// 通过 Model List 创建一个数据表
        /// </summary>
        /// <typeparam name="T">Model 的类型</typeparam>
        /// <param name="list">Model List</param>
        /// <param name="workbook">workbook</param>
        /// <returns></returns>
        public static ISheet CreateSheet<T>(this IWorkbook workbook, IList<T> list )
        {
            ISheet sheet = workbook.CreateSheet();

            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));

            IRow titleRow = sheet.CreateRow(0);
            ICell titleCell = titleRow.CreateCell(0, CellType.String);
            titleCell.SetCellValue(type.Name);

            titleRow.CreateCell(properties.Length, CellType.String).SetCellValue("123");

            IRow headRow = sheet.CreateRow(1);

            ICellStyle style = workbook.CreateCellStyle();
            style.FillForegroundColor = HSSFColor.Green.Index;  // 背景色
            style.FillPattern = FillPattern.SolidForeground;    // 

            for (int i = 0; i < properties.Length; i++)
            {
                ICell cell = headRow.CreateCell(i, CellType.String);
                cell.SetCellValue(properties[i].Name);
                cell.CellStyle = style;
            }
            for (int j = 0; j < list.Count; j++)
            {
                IRow row = sheet.CreateRow(j + 2);
                for (int i = 0; i < properties.Length; i++)
                {
                    ICell cell = row.CreateCell(i, CellType.String);
                    cell.SetCellValue(properties[i].GetValue(list[j]).ToString());
                }
            }

            return sheet;
        }

        #region 字典表
        static List<Engineering> engineerings = new List<Engineering>()
            {
                new Engineering()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "土建工程",
                },
                new Engineering()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "机电工程",
                },
                new Engineering()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "供电工程",
                },
            };
        static List<mType> types = new List<mType>()
            {
                new mType()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "安全隐患",
                },
                new mType()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "质量隐患",
                },
            };
        static List<Grade> grades = new List<Grade>()
            {
                new Grade()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "一级",
                },
                new Grade()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "二级",
                },
                new Grade()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "三级",
                },
                new Grade()
                {
                    Id = Guid.NewGuid().ToString(),
                    Name = "四级",
                },
            };
        #endregion

        public static List<Item> ToList(this IWorkbook workbook)
        {
            
            List<Category> categories = new List<Category>();
            List<Item> items = new List<Item>();

            foreach (var engineering in engineerings)
            {
                ISheet sheet = workbook.GetSheet(engineering.Name);

                if(sheet != null)
                {
                    string type = "";
                    string category = "";
                    string item = "";
                    string grade = "";
                    for (int i = 0; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if(row != null)
                        {
                            ICell one = row.GetCell(0);
                            if (one != null && !string.IsNullOrWhiteSpace(one.ToString()))
                                type = one.ToString();
                            ICell two = row.GetCell(1);
                            if (two != null && !string.IsNullOrWhiteSpace(two.ToString()))
                            {
                                category = two.ToString();
                                categories.Add(new Category()
                                {
                                    Id = Guid.NewGuid().ToString(),
                                    Name = category,
                                    MtypeId = types.Find(p => p.Name == type).Name
                                });
                            }
                            ICell four = row.GetCell(3);
                            if (four != null && !string.IsNullOrWhiteSpace(four.ToString()))
                                grade = four.ToString();
                            ICell three = row.GetCell(2);
                            if (three != null && !string.IsNullOrWhiteSpace(three.ToString()))
                            {
                                item = three.ToString();
                                items.Add(new Item()
                                {
                                    Id = Guid.NewGuid().ToString(),
                                    Name = item,
                                    GradeId = grade,
                                    CategoryId = category,
                                    EngineeringId = engineering.Name,
                                    MtypeId = types.Find(p => p.Name == type).Name
                                });
                            }

                        }
                    }
                }
            }
            return items;
        }

        public static IWorkbook CreateWorkbook(this HttpPostedFileBase file)
        {
            IWorkbook workbook = null;
            if (file != null && file.ContentLength > 0)
            {
                if (file.ContentType == "application/vnd.ms-excel")
                {
                    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.InputStream);
                    workbook = hssfworkbook;
                }
                else if (file.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    XSSFWorkbook xssfworkbook = new XSSFWorkbook(file.InputStream);
                    workbook = xssfworkbook;
                }
                else{
                    throw new ExcelHelperException("文件格式不正确。");
                }
            }
            else
            {
                throw new ExcelHelperException("文件不能为空。");
            }
            return workbook;
        }

    }

    /// <summary>
    /// 工程
    /// </summary>
    public class Engineering : Base
    {
    }

    /// <summary>
    /// 隐患类别 安全隐患 or 质量隐患
    /// </summary>
    public class mType : Base
    {
    }

    /// <summary>
    /// 隐患分类
    /// </summary>
    public class Category : Base
    {
        /// <summary>
        /// 类别Id
        /// </summary>
        public string MtypeId { get; set; }
    }
    
    /// <summary>
    /// 隐患项目
    /// </summary>
    public class Item : Base
    {
        /// <summary>
        /// 类别Id
        /// </summary>
        public string MtypeId { get; set; }
        /// <summary>
        /// 隐患分类Id
        /// </summary>
        public string CategoryId { get; set; }
        /// <summary>
        /// 等级Id
        /// </summary>
        public string GradeId { get; set; }
        /// <summary>
        /// 工程Id
        /// </summary>
        public string EngineeringId { get; set; }
    }

    /// <summary>
    /// 隐患等级
    /// </summary>
    public class Grade : Base
    {
    }

    public class Base
    {
        /// <summary>
        /// Id
        /// </summary>
        public string Id { get; set; }

        /// <summary>
        /// 类别名称
        /// </summary>
        public string Name { get; set; }
    }
}
